package com.yangcangclub.commons.utils;

import com.google.common.collect.Lists;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.Iterator;
import java.util.List;

/**
 * poi Excel处理类
 * Created by 肖明明 on 2016/12/8.
 */
public class ExcelHelper {

    /**
     * 读取Excel，xlsx
     */
    public static List<List> readExcel(InputStream is) throws IOException, InvalidFormatException {
        Workbook wb = WorkbookFactory.create(is);
        Iterator<Sheet> sheets=wb.sheetIterator();
        List<List> tableL= Lists.newArrayList();//返值table
        while(sheets.hasNext()) {
            Sheet sheet=sheets.next();
            int rowMaxIndex=sheet.getLastRowNum()==0?1:sheet.getLastRowNum()+1;//这个从0开始
            for(int r=0;r<rowMaxIndex;r++){
                List rowL=Lists.newArrayList();//行
                Row row=sheet.getRow(r);
                if(row==null)continue;
                int cellMaxIndex=row.getLastCellNum();//这个从1开始。POI的api里的傻逼搞法
                for(int c=0;c<cellMaxIndex;c++){
                    Cell cell=row.getCell(c);
                    Object value=getCellValueAsString(cell);
                    rowL.add(value);
                }
                tableL.add(rowL);
            }
        }
        return tableL;
    }

    
    /**
     * 写Excel，xlsx
     */
    public static void writeExcel(List<List> rows,OutputStream stream) throws IOException {
        if(rows==null||rows.size()==0) return;
        Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("sheet01");
        for(int i=0;i<rows.size();i++){
            Row sheetRow = sheet.createRow(i);//创建行
            List l=rows.get(i);//取出值
            if(l==null||l.size()==0) continue;
            for(int j=0;j<l.size();j++){//遍历row
                setCellValue(sheetRow.createCell(j),l.get(j));
            }
        }
        wb.write(stream);
        wb.close();
        stream.flush();
        stream.close();
    }


    /**
     * 取Cell值
     * @param cell
     * @return  String or Number or null
     */
    private static Object getCellValueAsString(Cell cell){
        if(cell==null) return null;
        int n=cell.getCellType();
        Object value=null;
        switch (n){//有一些不处理，全部返回null，仅处理数字和字符串
//            case -1:value=cell.getStringCellValue();break;
            case 0:value=cell.getNumericCellValue();break;
            case 1:value=cell.getStringCellValue();break;
            //case 2:value=cell.getCellFormula();break;
            //case 3:value=cell.getStringCellValue();break;
            //case 4:value=cell.getBooleanCellValue();break;
            //case 5:value=cell.getErrorCellValue();break;
            default:value=null;
        }
        return value;
    }

    /**
     * 设置Cell值
     * @param cell
     * @param b
     */
    private static void setCellValue(Cell cell,Object b){
        if(b==null){
            cell.setCellValue("");
        }else if(b instanceof Number){
            Double m=new Double(b.toString());
            cell.setCellValue(m);
        }else if(b instanceof Boolean) {
            cell.setCellValue((Boolean) b);
        }else{
            cell.setCellValue(b.toString());
        }

    }
}
